package dbclass;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import classes.DbClass;

import beans.Noticia;
import beans.Video;

public class VideoJDBC implements VideoDAO{	

	private Connection con;
	
	
	public Video insert(Video video) throws SQLException, ClassNotFoundException
	{
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		boolean check =  stm.execute("INSERT INTO video (id, url, titulo) VALUES (" +
				"NULL, '" + video.getUrl() + "', '" + video.getTitulo() + "')");
		

		if(check)
		{
			return video;
		}	
		return null;	
	}
	
	public Video update(Video video) throws SQLException, ClassNotFoundException
	{
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		boolean check = stm.execute("UPDATE video SET url = '" + video.getUrl() + "', titulo = '" 
				+ video.getTitulo() + "' WHERE id = '" + video.getId() + "'");
		
		if(check)
		{
			return video;
		}	
		return null;
	}

	public boolean delete(long id) throws ClassNotFoundException, SQLException {
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		stm.execute("DELETE FROM noticia_video WHERE id_video = '" + id + "' LIMIT 1");
		int check = stm.executeUpdate("DELETE FROM video WHERE id = '" + id + "' LIMIT 1");
		if(check > 0)
			return true;
		return false;
	}
	
	public List<Noticia> findAllNoticias(long id) throws SQLException,
	ClassNotFoundException {
		List<Noticia> noticias = new ArrayList<Noticia>();
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT noticia.* FROM noticia " +
				"LEFT JOIN noticia_video ON noticia.id = noticia_video.id_video " +
				"AND noticia_video.id_video  = '" + id + "')");
		
		while (result.next()) {
			Noticia noticia = new Noticia(result.getLong(1), result.getString(2), result.getString(3), result.getString(4), 
					result.getLong(5), result.getLong(6));;
			noticias.add(noticia);
		}		
		return noticias;
	}

	public List<Video> findAll() throws SQLException, ClassNotFoundException {
		List<Video> videoes = new ArrayList<Video>();
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM video");
		
		while (result.next()) {
			Video video = new Video(result.getLong(1), result.getString(2), result.getString(3));
            videoes.add(video);
        }		
		return videoes;
	}


	public Video findById(long id) throws SQLException, ClassNotFoundException {
		Video video = null;
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM video WHERE id = '" + id + "'");
		
		while (result.next()) {
			video = new Video(result.getLong(1), result.getString(2), result.getString(3));
        }		
		return video;
	}
	//Retorna os videos relacionados a esta imagem
	public List<Video> findUsados(long id) throws SQLException, ClassNotFoundException {
		List<Video> videos = new ArrayList<Video>();
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM video WHERE id  IN (SELECT id_video FROM noticia_video where id_noticia = '"+id+"') ");
		
		while (result.next()) {
			Video video = new Video(result.getLong(1), result.getString(2), result.getString(3));
			videos.add(video);
        }		
		return videos; 
	}	

	//Retorna os videos nao relacionados a esta imagem
	public List<Video> findNaoUsados(long id) throws SQLException, ClassNotFoundException {
		List<Video> videos = new ArrayList<Video>();
		
		con = DbClass.getConnection();
		Statement stm = con.createStatement();
		
		ResultSet result;
		result = stm.executeQuery("SELECT * FROM video WHERE id  NOT IN (SELECT id_video  FROM noticia_video where id_noticia = '"+id+"') ");
		
		while (result.next()) {
			Video video = new Video(result.getLong(1), result.getString(2), result.getString(3));
			videos.add(video);
        }		
		return videos; 
	}
}